package com.sf.dlb.jpa.repository;

import com.sf.dlb.jpa.model.UserInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 用户 Repository
 *
 * Created by jing.tan on 2018/12/10.
 */
@Repository
public interface UserInfoRepository extends JpaRepository<UserInfo, Long> {
    /**
     * 根据手机号查询
     * @param mobile
     * @param delFlag
     * @return
     */
    UserInfo findByMobileAndDelFlag(String mobile, String delFlag);
    /**
     * 根据用户总数
     * @return
     */
    @Query(value = "SELECT max(a.user_id+0) FROM `user_info` a ",nativeQuery = true)
    Long findByDelFlagCount();
    /**
     * 根据用户ID查询
     * @return
     */
    UserInfo findByUserIdAndDelFlag(String userId, String delFlag);
    /**
     * 根据token查询用户
     * @return
     */
    UserInfo findByTokenAndDelFlag(String token, String delFlag);
    /**
     * 查询购买了等级的用户
     * @return
     */
    @Query(value="SELECT * FROM user_info a WHERE a.user_grade!='0' AND a.del_flag='0' AND a.user_grade!='' AND a.user_grade IS NOT NULL",nativeQuery = true)
    List<UserInfo> findByBuyGradeAll();
    /**
     * 查询购买了等级的用户，并等级已经过了有效期
     * @return
     */
    @Query(value="SELECT * FROM user_info a WHERE a.user_grade!='0' AND a.user_grade!='' AND a.user_grade IS NOT NULL AND DATE_FORMAT(a.grade_validity_time,'%Y-%m-%d')=:validityTime",nativeQuery = true)
    List<UserInfo> findByBuGradeAndOverdueAll(@Param("validityTime") String validityTime);
    /**
     * 根据邀请码查询用户
     * @param qrCode
     * @param delFlag
     * @return
     */
    UserInfo findByQrCodeAndDelFlag(String qrCode, String delFlag);
}